AthenaのSELECTクエリ結果にダブルクォーテーション(“)を含みたくないときは、CTASクエリを使おう

Athena のクエリ結果ファイルに、ダブルクォーテーション(")を含まないように出力する方法を検証しました。サンプルデータは趣味です。

#Amazon Athena

#AWS

もっさん

2021.07.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

おはこんばんちは。オペレーション部のもっさんです。
データソース(csvファイル)にはダブルクォーテーションを含んでいないのに、 Athena を通して実行したクエリ結果をファイルでダウンロードすると、各要素がダブルクォーテーション(")で囲まれて出力されているといった事象に遭遇しました。
データソースからあらかじめダブルクォーテーションを取り除く処理を行っていた場合など、クエリ結果ファイルにダブルクォーテーションを含みたくないケースがあるかと思います。今回はデータソースの要素そのままに、ダブルクォーテーションの自動付与をしないでファイル出力する方法を検証しました。

前提

サンプルのデータソースとなるCSVファイルはS3バケットに保存しており、 Athena のクエリ実行は Athena コンソールから実行しています。
ファイルの中身は以下の形式です。

ID,放送開始,放送終了,タイトル,主人公
1,2000/1/30,2001/1/21,仮面ライダークウガ,五代雄介
2,2001/1/28,2002/1/27,仮面ライダーアギト,津上翔一
3,2002/2/3,2003/1/19,仮面ライダー龍騎,城戸真司
4,2003/1/26,2004/1/18,仮面ライダー555,乾巧
5,2004/1/25,2005/1/23,仮面ライダー剣,剣崎一真
6,2005/1/30,2006/1/22,仮面ライダー響鬼,ヒビキ
7,2006/1/29,2007/1/21,仮面ライダーカブト,天道総司
8,2007/1/28,2008/1/20,仮面ライダー電王,野上良太郎
9,2008/1/27,2009/1/18,仮面ライダーキバ,紅渡
10,2009/1/25,2009/8/30,仮面ライダーディケイド,門矢士
11,2009/9/6,2010/8/29,仮面ライダーW,左翔太郎&フィリップ
12,2010/9/5,2011/8/28,仮面ライダーOOO,火野映司
13,2011/9/4,2012/8/26,仮面ライダーフォーゼ,如月弦太朗
14,2012/9/2,2013/9/29,仮面ライダーウィザード,操真晴人
15,2013/10/6,2014/9/28,仮面ライダー鎧武,葛葉紘太
16,2014/10/5,2015/9/27,仮面ライダードライブ,泊進ノ介
17,2015/10/4,2016/9/25,仮面ライダーゴースト,天空寺タケル
18,2016/10/2,2017/8/27,仮面ライダーエグゼイド,宝生永夢
19,2017/9/3,2018/8/26,仮面ライダービルド,桐生戦兎
20,2018/9/2,2019/8/25,仮面ライダージオウ,常磐ソウゴ
21,2019/9/1,2020/8/30,仮面ライダーゼロワン,飛電或人
22,2020/9/6,,仮面ライダーセイバー,神山飛羽真

CREATE EXTERNAL TABLE句で、テーブルを作成します。

CREATE EXTERNAL TABLE `test_rider`(
`ID` string ,
`start` string ,
`end` string ,
`title` string ,
`hero` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://mybucket/'
TBLPROPERTIES (
'has_encrypted_data'='false',
'skip.header.line.count'='1')

サンプルデータは1行目がヘッダになっているので、読み込みをスキップします。
skip.header.line.count プロパティで設定が可能ですが、本記事では詳しく触れません。こちらのブログ記事に詳しく解説されていますので、ご確認ください。

Athena コンソールの左ペインの[Tables]に test_rider が表示されていれば、テーブルが無事作成されています。
このテーブルに対し SELECT 句を実施して、最初の10件のデータを抽出してみましょう。

SELECT * FROM "mydatabase"."test_rider" limit 10;

Athena コンソールから、抽出結果をファイルとしてダウンロードします。クエリ結果が表示されているペインの右上部のボタンをクリックします。

ダウンロードしたファイルをテキストエディタで開いてみました。
データソースの各要素はダブルクォーテーション(")で囲まれていなかったにも関わらず、クエリ結果をダウンロードしたファイルではダブルクォーテーションが付与されていることがわかります。

CTASクエリを使って結果をS3に出力すると解決

今回のように、クエリ結果のファイル出力形式をデータソースと一致させたい場合は、「CREATE TABLE AS SELECT(CTAS)」クエリを使って S3 上に テキストファイル として出力することで、ダブルクォーテーションなしのファイルを作成することができます。

CREATE TABLE IF NOT EXISTS test_rider_result2
  WITH (format='TEXTFILE', external_location='s3://mybucket-outputs/file/', field_delimiter = ',') AS
SELECT * FROM "mydatabase"."test_rider" limit 10;

出力したファイルをテキストエディタで開いた結果がこちらです。
format='TEXTFILE'を指定しているため、 S3 に作成されるファイルの形式はCSVではなくテキスト形式になることに注意してください。